﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using UnityScriptEngine;
using StoreClientModule;

namespace MBBStoreClient
{
    public partial class frmStatementOfAccountManager : Form
    {
        UnityProcessor proc = new UnityProcessor();
        Threader t = new Threader();
        UnityScript[] dat = new UnityScript[0];
        DataTable accountName = new DataTable();
        
        DataTable dt = new DataTable();
        DataTable details = new DataTable();
        DataTable items = new DataTable();
        DataTable payments = new DataTable();
        DataTable balance = new DataTable();
        DataRow  [] invoices = new DataRow[0];
        DataTable result = new DataTable();

        string filter = "";
        string dateFilter = "";
        string myDate = "";
        string month="";
        string[] arrDate;

        public frmStatementOfAccountManager()
        {
            InitializeComponent();
            InitializeFrameSliderForm();
        }
        private void InitializeForm()
        {
            InitLookUp();
            txtFilter.SelectedIndex = 0;
            txtFilterDate.SelectedIndex = 0;
            proc.ProcessCompleted += new ProcessHandler(proc_ProcessCompleted);
            proc.ProcessError += new ProcessHandler(proc_ProcessError);
            t.MethodCompleted += new EventHandler(t_MethodCompleted);
            btnSearch.Click += new EventHandler(btnSearch_Click);
            grdItems.DoubleClick += new EventHandler(grdItems_DoubleClick);
            txtAccountName.KeyPress += new KeyPressEventHandler(txtSearch_KeyPress);
            txtAccountName.Focus();
        }

        void txtSearch_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)13)
            {
                btnSearch_Click(null, null);
            }
        }

      
        void grdItems_DoubleClick(object sender, EventArgs e)
        {
            Common.parent.ViewChargeInvoice(grdItems.Rows[grdItems.SelectedRows[0].Index].Cells[1].Value.ToString());            
        }
        void btnSearch_Click(object sender, EventArgs e)
        {
            if (!txtAccountName.Text.Equals(""))
            {
                month = "";
                groupBox1.Enabled = false;
                pnlMsg.Visible = true;
                dateFilter = txtFilterDate.Text;
                myDate = txtDate.Text;
                arrDate = myDate.Split('/');
                month = int.Parse(arrDate[0]).ToString();
                filter = txtFilter.Text;
                t.MethodCall(Search);
            }
        }
        void proc_ProcessError(string id, string desc)
        {
            t.SafeCall(this, delegate
            {
                MessageBox.Show(desc);
            });
        }
        void proc_ProcessCompleted(string id, string desc)
        {
            t.SafeCall(this, delegate
            {
                switch (id)
                {
                    case "INIT":
                        proc.DBPUX_Data_Fetch(out dat);

                        accountName = dat[0].ToDataTable();
                        if (!txtAccountName.Focused)
                        {
                            txtAccountName.AutoCompleteCustomSource.AddRange(dat[0].ToList<string>(0));
                        }
                        groupBox1.Enabled = true;
                        break;
                    case "SEARCH":
                        proc.DBPUX_Data_Fetch(out dat);

                        details = dat[0].ToDataTable();
                        items = dat[1].ToDataTable();
                        payments = dat[2].ToDataTable();
                        balance = dat[3].ToDataTable();

                        t.MethodCall(CreateDataTable);
                        break;
                }
            });
        }
        void t_MethodCompleted(object sender, EventArgs e)
        {
            t.SafeCall(this, delegate
            {
                switch (sender.ToString())
                {
                    case "CreateDataTable":
                        switch (filter)
                        {
                            case "All": grdItems.DataSource = dt; 
                                lblStatus.Text = dt.Rows.Count + " Results(s) @ " + proc.ProcEnd() + " sec.";
                                FormatGrid();
                                groupBox1.Enabled = true;
                                pnlMsg.Visible = false;
                                break;
                            case "With Balance": invoices = dt.Select("[Total Balance] <> '0'");
                                t.MethodCall(generateFinalResult);
                                break;
                            case "Fully Paid": invoices = dt.Select("[Total Balance] = '0'");
                                t.MethodCall(generateFinalResult);
                                break;
                        }
                        break;
                    case "generateFinalResult":
                        grdItems.DataSource = result;
                        lblStatus.Text = result.Rows.Count + " Results(s) @ " + proc.ProcEnd() + " sec.";
                        FormatGrid();

                        groupBox1.Enabled = true;
                        pnlMsg.Visible = false;
                        break;
                }
            });
        }
        //Operations
        void InitLookUp()
        {
            groupBox1.Enabled = false;
            while (proc.IsRunning)
            {
                System.Threading.Thread.Sleep(1);
            }
            UnityScript x = new UnityScript("INIT", 4);
            x.Script_Add(Script.DBPUX_BEGIN);
            x.Script_Add(Script.DBPUX_QUERY, "SELECT DISTINCT fSoldTo FROM tblSalesDetail WHERE fInvoiceType='Charged'");
            x.Script_Add(Script.DBPUX_COMMIT);
            proc.Run(x);
        }
        void Search()
        {
            while (proc.IsRunning)
            {

            }
            proc.ProcStart();
            string src = txtAccountName.Text;
            UnityScript u = new UnityScript("SEARCH", 7);

            u.Script_Add(Script.DBPUX_BEGIN);
            string sql = "";

            switch (dateFilter)
            {
                case "All":
                    sql = "";
                    break;
                case "Year":
                    sql = " AND fDate LIKE '%"+arrDate[1]+"'";
                    break;
                case "Month":
                    sql = " AND fDate LIKE '" + month + "%'";
                    break;
                case "MonthYear":
                    sql = " AND fDate LIKE '" + month + "%"+arrDate[1]+"'";
                    break;
            }

            u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fDate, fInvoice, fStamp FROM tblsalesdetail WHERE (fSoldTo = '{0}' AND fInvoiceType = 'Charged' " + sql + ")", src));
            u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, SUM(fQty * fUnitPrice), fInvoice, fStamp FROM tblsalesitem GROUP BY fInvoice"));
            u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, SUM(fAmount), fInvoiceNumber, fStamp FROM tblchargepayments GROUP BY fInvoiceNumber"));
            u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fTerms, fPercentage, fInvoice, fStamp FROM tblchargebalance WHERE fStatus = '1'"));
            /*
            switch (filter)
            {
                case "All":
                    

                        //u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fDate AS 'Date', t1.fInvoice AS 'Charged Invoice', ffAmount AS 'Total Amount', ffPayment AS 'Total Payment', (ffAmount- ffPayment) AS Balance, ffTerms AS Terms, ffPercentage AS Interest, fStamp " +
                        //    "FROM tblsalesdetail t1, (SELECT SUM(fQty * fUnitPrice) AS ffAmount, t22.fInvoice FROM tblsalesitem t22 GROUP BY t22.fInvoice) AS t2, " +
                        //    "(SELECT SUM(fAmount) AS ffPayment, t33.fInvoiceNumber FROM tblchargepayments t33 GROUP BY t33.fInvoiceNumber) AS t3, " +
                        //    "(SELECT fTerms AS ffTerms, fPercentage AS ffPercentage, t44.fInvoice FROM tblchargebalance t44 GROUP BY t44.fInvoice) AS t4 " +
                        //    "WHERE (((t1.fInvoice=t2.fInvoice) AND (t1.fInvoice=t3.fInvoiceNumber) AND (t1.fInvoice=t4.fInvoice)) AND (fSoldTo = '{0}' AND fInvoiceType = 'Charged' "+sql+")) ", src));

                    break;
                case "With Balance":
                    u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fDate AS 'Date', t1.fInvoice AS 'Charged Invoice', ffAmount AS 'Total Amount', ffPayment AS 'Total Payment', (ffAmount- ffPayment) AS Balance, ffTerms AS Terms, ffPercentage AS Interest, fStamp " +
                        "FROM tblsalesdetail t1, (SELECT SUM(fQty * fUnitPrice) AS ffAmount, t22.fInvoice FROM tblsalesitem t22 GROUP BY t22.fInvoice) AS t2, " +
                        "(SELECT SUM(fAmount) AS ffPayment, t33.fInvoiceNumber FROM tblchargepayments t33 GROUP BY t33.fInvoiceNumber) AS t3, " +
                        "(SELECT fTerms AS ffTerms, fPercentage AS ffPercentage, t44.fInvoice FROM tblchargebalance t44 GROUP BY t44.fInvoice) AS t4 " +
                        "WHERE (((t1.fInvoice=t2.fInvoice) AND (t1.fInvoice=t3.fInvoiceNumber) AND (t1.fInvoice=t4.fInvoice)) AND (fSoldTo = '{0}' AND fInvoiceType = 'Charged' " + sql + ") AND ((ffAmount - ffPayment) <> 0)) ", src));
                    break;
                case "Fully Paid":
                    u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fDate AS 'Date', t1.fInvoice AS 'Charged Invoice', ffAmount AS 'Total Amount', ffPayment AS 'Total Payment', (ffAmount- ffPayment) AS Balance, ffTerms AS Terms, ffPercentage AS Interest, fStamp " +
                        "FROM tblsalesdetail t1, (SELECT SUM(fQty * fUnitPrice) AS ffAmount, t22.fInvoice FROM tblsalesitem t22 GROUP BY t22.fInvoice) AS t2, " +
                        "(SELECT SUM(fAmount) AS ffPayment, t33.fInvoiceNumber FROM tblchargepayments t33 GROUP BY t33.fInvoiceNumber) AS t3, " +
                        "(SELECT fTerms AS ffTerms, fPercentage AS ffPercentage, t44.fInvoice FROM tblchargebalance t44 GROUP BY t44.fInvoice) AS t4 " +
                        "WHERE (((t1.fInvoice=t2.fInvoice) AND (t1.fInvoice=t3.fInvoiceNumber) AND (t1.fInvoice=t4.fInvoice)) AND (fSoldTo = '{0}' AND fInvoiceType = 'Charged' " + sql + ") AND ((ffAmount - ffPayment) = 0)) ", src));
                    break;
            }
            */
            u.Script_Add(Script.DBPUX_COMMIT);
            proc.Run(u);
        }
        void Search2()
        {
            while (proc.IsRunning)
            {

            }
            proc.ProcStart();
            string src = txtAccountName.Text;
            UnityScript u = new UnityScript("SEARCH", 5);
            
            u.Script_Add(Script.DBPUX_BEGIN);
            u.Script_Add(Script.DBPUX_GENERIC, "DROP TEMPORARY TABLE IF EXISTS tmpsupplierprofile");
            u.Script_Add(Script.DBPUX_GENERIC, "CREATE TEMPORARY TABLE tmpsupplierprofile AS (SELECT * FROM tblsupplierprofile)");
            switch (filter)
            {
                case "All":
                    u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fNumber AS 'P.O. Number', fRefNum AS 'Reference Number', fDate AS 'Date', fTerms AS 'Terms',  fStamp FROM tblpurchaseorder WHERE " +
                        "fNumber LIKE '%{0}%' OR fRefNum IN (SELECT fRefNum FROM tmpsupplierprofile WHERE fName LIKE '%{0}%') " +
                        "OR fDate LIKE '%{0}%' OR fTerms LIKE '%{0}%' AND (fNumber IN (SELECT fNumber FROM tblpurchaseorderpayments)) ORDER BY fRefNum ASC, fNumber DESC", src));
                    break;
                case "With Payments":
                    u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fNumber AS 'P.O. Number', fRefNum AS 'Reference Number', fDate AS 'Date', fTerms AS 'Terms', fStamp  FROM tblpurchaseorder WHERE " +
                        "(fNumber LIKE '%{0}%' OR fRefNum IN (SELECT fRefNum FROM tmpsupplierprofile WHERE fName LIKE '%{0}%') " +
                        "OR fDate LIKE '%{0}%' OR fTerms LIKE '%{0}%') AND (fNumber IN (SELECT fNumber FROM tblpurchaseorderpayments)) ORDER BY fRefNum DESC, fNumber DESC", src));
                    break;
                case "Without Payments":
                    u.Script_Add(Script.DBPUX_QUERY, string.Format("SELECT fID, fNumber AS 'P.O. Number', fRefNum AS 'Reference Number', fDate AS 'Date', fTerms AS 'Terms', fStamp  FROM tblpurchaseorder WHERE " +
                        "(fNumber LIKE '%{0}%' OR fRefNum IN (SELECT fRefNum FROM tmpsupplierprofile WHERE fName LIKE '%{0}%') " +
                        "OR fDate LIKE '%{0}%' OR fTerms LIKE '%{0}%') AND (fNumber NOT IN (SELECT fNumber FROM tblpurchaseorderpayments)) ORDER BY fRefNum DESC, fNumber DESC", src));
                    break;
            }

            u.Script_Add(Script.DBPUX_QUERY, "SELECT * FROM tmpsupplierprofile");
            u.Script_Add(Script.DBPUX_COMMIT);
            proc.Run(u);
        }

        void CreateDataTable()
        {
            dt = new DataTable();
            dt.Columns.Add("Date");
            dt.Columns.Add("Charged Invoice");
            dt.Columns.Add("Amount");
            dt.Columns.Add("Total Payment");
            dt.Columns.Add("Total Balance");
            dt.Columns.Add("Terms");
            dt.Columns.Add("Percentage");


            for (int i = 0; i < details.Rows.Count; i++)
            {
                DataRow dr = dt.NewRow();
                dr["Date"]= details.Rows[i]["fDate"].ToString();
                string cInvoice = details.Rows[i]["fInvoice"].ToString();
                dr["Charged Invoice"] = cInvoice;
                
                //item total amount
                for (int j = 0; j < items.Rows.Count; j++)
                {
                    string _cInvoice = items.Rows[j]["fInvoice"].ToString();
                    if (cInvoice.Equals(_cInvoice))
                    {
                        dr["Amount"] = items.Rows[j][0].ToString();
                    }
                }
                //payments 
                bool withPayment = false;
                for (int j = 0; j < payments.Rows.Count; j++)
                {
                    string _cInvoice = payments.Rows[j]["fInvoiceNumber"].ToString();
                    if (cInvoice.Equals(_cInvoice))
                    {
                        dr["Total Payment"] = payments.Rows[j][0].ToString();
                        withPayment = true;
                    }
                }
                if (withPayment == false)
                {
                    dr["Total Payment"] = "0.00";
                }
                //terms and percentage
                for (int j = 0; j < balance.Rows.Count; j++)
                {
                    string _cInvoice = balance.Rows[j]["fInvoice"].ToString();
                    if (cInvoice.Equals(_cInvoice))
                    {
                        dr["Terms"] = balance.Rows[j]["fTerms"].ToString();
                        dr["Percentage"] = balance.Rows[j]["fPercentage"].ToString();
                        withPayment = true;
                    }
                }
                dr["Total Balance"] = float.Parse(dr["Amount"].ToString()) - float.Parse(dr["Total Payment"].ToString());
                dt.Rows.Add(dr);
            }

        }
        void generateFinalResult()
        {
            result = dt.Clone();
            for (int i = 0; i < invoices.Length; i++)
                result.ImportRow(invoices[i]);
        }
        void FormatGrid()
        {

            grdItems.Columns[0].Width = (grdItems.Width * 10) / 100;
            grdItems.Columns[1].Width = (grdItems.Width * 23) / 100;
            grdItems.Columns[2].Width = (grdItems.Width * 15) / 100;
            grdItems.Columns[3].Width = (grdItems.Width * 15) / 100;
            grdItems.Columns[4].Width = (grdItems.Width * 15) / 100;
            grdItems.Columns[5].Width = (grdItems.Width * 10) / 100;
            grdItems.Columns[6].Width = (grdItems.Width * 10) / 100;

            grdItems.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft;
            grdItems.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            grdItems.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdItems.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdItems.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            grdItems.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            grdItems.Columns[6].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

            if (grdItems.Rows.Count > 0)
            {
                for (int i = 0; i < grdItems.Rows.Count; i++)
                {
                    //grdItems.Rows[i].Cells[2].Value = grdItems.Rows[i].Cells[2].Value.ToString().Split(' ')[0];
                    
                    grdItems.Rows[i].Cells[2].Value = float.Parse(grdItems.Rows[i].Cells[2].Value.ToString()).ToString("#,#0.00");
                    grdItems.Rows[i].Cells[3].Value = float.Parse(grdItems.Rows[i].Cells[3].Value.ToString()).ToString("#,#0.00");
                    grdItems.Rows[i].Cells[4].Value = float.Parse(grdItems.Rows[i].Cells[4].Value.ToString()).ToString("#,#0.00");
                    
                }
            }
        }
      
    }
}
